﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Data;
using SdmxMl.SmsImportCfg;
using SdmxMl.Common;
using SdmxMl.Structure;
using SdmxMl.Manager;

namespace PluginMeb
{
    public class ImportHclKombi
    {
         #region Internal Members

        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        private CodeListType[] arrCodelistLevel;
        private HierarchicalCodelistType hcl;
        private HierarchyType hierarchy;

        private SmManager sm;
        #endregion

        public const string AGENCY_ID = "CH1_MEB";

        private CodeListType clSchulart;
        private CodeListType clAusbild;
        public ImportHclKombi(string path)
        {
            sm = new SmManager();
            arrCodelistLevel = new CodeListType[2];
            clSchulart = new CodeListType();
            clSchulart.AgencyId = AGENCY_ID;
            clSchulart.Id = "CL_SCHULART";
            clSchulart.Name.UpdateForLanguage("de", "CL_SCHULART");
            arrCodelistLevel[0] = clSchulart;

            clAusbild = new CodeListType();
            clAusbild.AgencyId = AGENCY_ID;
            clAusbild.Id = "CL_AUSBILDUNGSFORM";
            clAusbild.Name.UpdateForLanguage("de", "Gesetze Artikel");
            arrCodelistLevel[1] = clAusbild;

            spreadsheetConcept = LoadSources(path);

            hcl = new HierarchicalCodelistType();
            hcl.AgencyId = AGENCY_ID;
            hcl.Id = "HCL_KOMBINATION";
            hcl.Name.UpdateDefaultText("Kombination");
            hcl.HierarchyList.Clear();
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, hcl);


            for (int i = 0; i < arrCodelistLevel.Length; i++)
            {
                 CodelistRefType clRef = new CodelistRefType();
                 clRef.AgencyId = AGENCY_ID;
                clRef.Alias = (i+1).ToString();
                clRef.Id = arrCodelistLevel[i].Id;
                clRef.Version = arrCodelistLevel[i].Version;                 
                hcl.CodelistRefList.Add(clRef);
                sm.PutArtefact(SdmxArtefactType.CodeLists, arrCodelistLevel[i]);
            }

            hierarchy = new HierarchyType(hcl.CodelistRefList);
            hierarchy.Id = "HR_KOMBINATION";
            hierarchy.Name.UpdateDefaultText("Hierarchy");
            hcl.HierarchyList.Add(hierarchy);

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                try
                {
                    CodeType code = new CodeType();
                    string c = wh.GetCellContent(row, 1);
                    if (c.Length == 0)
                        break;

                    string p = wh.GetCellContent(row, 2);
                    string level = wh.GetCellContent(row, 3);
                    //string vf = wh.GetDateStr(row, 4);
                    //string vt = wh.GetDateStr(row, 5);
                    string vf = wh.GetCellContent(row, 4).Trim('.');
                    string vt = wh.GetCellContent(row, 5).Trim('.');

                    code.Id = c;

                    switch (level)
                    {
                        case"1":
                            clSchulart.CodeList.Add(code);

                        break;

                        case "2":
                            clAusbild.CodeList.Add(code);
                        break;

                    }

                    SetHierarchyRef(code, int.Parse(level), p, vf, vt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            XmlDocument xDoc = sm.GetAsDocument();
            xDoc.Save("c:\\doc\\meb\\hclh.xml");
            
        }

        private void SetHierarchyRef(CodeType c, int level, string parentCode, string vf, string vt)
        {
            CodeRefTypeList crefList = hierarchy.CodeRefList;
            if (level == 2)
            {
                // get parent
                foreach (CodeRefType cRef in  hierarchy.CodeRefList)
                    if (cRef.CodeID == parentCode)
                    {
                        crefList = cRef.CodeRefList;
                        break;

                    }
            }
            else if (level == 3)
            {
                // get parent
                foreach (CodeRefType cRef in hierarchy.CodeRefList)
                {
                    foreach (CodeRefType cRefchild in cRef.CodeRefList)
                    {
                        if (cRefchild.CodeID == parentCode)
                        {
                            crefList = cRefchild.CodeRefList;
                            break;
                        }
                    }
                    if (crefList != hierarchy.CodeRefList)
                        break;
                }
            }


            CodeRefType cref = new CodeRefType(c.Id, level.ToString());
            if (vf.Length == 4)
            {
                cref.ValidFrom = vf + "-01-01";
            }
            if (vt.Length == 4)
            {
                cref.ValidTo = vt + "-01-01"; ;
            }
            crefList.Add(cref);

        }


        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }   }
}
